package com.version1_1.dao.implement;

import com.SQLGroup.SQLAccess;
import com.mysql.jdbc.Statement;
import com.util.property.Properties;
import com.version1_1.dao.PageDao;
import com.version1_1.model.Focus;
import com.version1_1.model.MainPageModel;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

@Repository
public class PageDaoImpl implements PageDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /*logger使用对象创建*/
    private static final Logger LOGGER = Logger.getLogger(PageDaoImpl.class);

    /**
     * @param commentString
     * @return
     * @name: setPageComment
     * @description: 在数据库中更新评论的点赞数或者新增一条评论（包括回复）
     */
    @Override
    public boolean setPageComPraise(String commentString) {
        JSONObject jsonObject = JSONObject.fromObject(commentString);
        int commentType = jsonObject.getInt("commentType");
        int result = 0;

        switch (commentType) {
            case -1:
                /**
                 * 1. 取消对一条评论的点赞
                 * {"commentType":1,"commentId":,"senderId":,"receiverId":,"date":""}
                 */
                int commentId = jsonObject.getInt("commentId");
                int senderId1 = jsonObject.getInt("senderId");

                try {
                    result = jdbcTemplate.update(SQLAccess.DeleteACommnetPraise, new Object[]{commentId, senderId1});
                } catch (DataAccessException exception) {
                    LOGGER.error("取消对一条评论的点赞出错...", exception);
                }
                break;
            case 1:
                /**
                 * 2. 对一条评论进行点赞
                 */
                int comId = jsonObject.getInt("commentId");
                int senderId = jsonObject.getInt("senderId");
                int receiverId = jsonObject.getInt("receiverId");
                String date = jsonObject.getString("date");

                try {
                    result = jdbcTemplate.update(SQLAccess.AddACommentPraise, new Object[]{comId, senderId, receiverId, date});
                } catch (DataAccessException exception) {
                    LOGGER.error("对一条评论进行点赞出错...", exception);
                }
                break;

            default:
                break;
        }

        return result == 0 ? false : true;
    }

    /**
     * @param commentString
     * @return
     * @Description: 新增一条评论或回复
     * @Title: setPageComment
     */
    @Override
    public int setPageComment(String commentString) {
        JSONObject jsonObject = JSONObject.fromObject(commentString);

        KeyHolder key = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection conn)
                    throws SQLException {

                PreparedStatement preStatement = conn.prepareStatement(
                        SQLAccess.AddACommnet, Statement.RETURN_GENERATED_KEYS);
                preStatement.setInt(1, jsonObject.getInt("pageId"));
                preStatement.setInt(2, jsonObject.getInt("senderId"));
                preStatement.setInt(3, jsonObject.getInt("toComId"));
                preStatement.setString(4, jsonObject.getString("date"));
                preStatement.setString(5, jsonObject.getString("comment"));
                return preStatement;
            }
        }, key);

        // 如果这是一条回复则更新原来的评论的hasReply为1
        if (jsonObject.getInt("toComId") != -1) {
            int updateRow = jdbcTemplate.update(SQLAccess.UpdateHasreplyTrue, new Object[]{jsonObject.getInt("toComId")});
            if (updateRow == 0) {
                LOGGER.error("新增评论或回复，改变原先评论的hasReply为1出错");
            }
        }
        return key.getKey().intValue();
    }

    /**
     * @param pageId
     * @param pageNumber
     * @param pageSize
     * @return
     * @name: getCommentList
     * @description: 根据pageId得到文章评论和回复列表
     */
    @Override
    public List getCommentList(int pageId, int pageNumber, int pageSize) {

        @SuppressWarnings("deprecation")
        int count = jdbcTemplate
                .queryForInt(SQLAccess.SelectPageCommentCount, new Object[]{pageId});
        /*页数超过总页数...*/
        if ((pageNumber - 1) * pageSize >= count) {
            LOGGER.info("根据pageId得到文章评论和回复列表,请求页数超过总页数...");
            return null;
        }

        return jdbcTemplate.queryForList(SQLAccess.SelectPageComment, new Object[]{
                pageId, (pageNumber - 1) * pageSize, pageSize});
    }

    /**
     * @param key
     * @return
     * @name: searchPage
     * 页面标题搜索
     */
    @Override
    public List<Map<String, Object>> searchPage(String key) {
        String sql = "SELECT pageId,firstTitle,secondType,thirdTypeName,imageSrc FROM pagepre_firsttype WHERE firstTitle LIKE '%" + key + "%'";
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * @param commentId
     * @param userId
     * @return
     * @name: checkPraised
     * @description: 检测是否点赞过
     */
    @Override
    public boolean checkPraised(int commentId, int userId) {

        try {
            jdbcTemplate.queryForObject(SQLAccess.CheckIsUserPraiseComment, new Object[]{commentId, userId}, Integer.class);
        } catch (EmptyResultDataAccessException exception) {
            LOGGER.info("用户未对该文章点赞...");
            return false;
        }
        return true;
    }

    /**
     * @param pageId
     * @return
     * @name: getPageContent
     * @description: 根据pageId得到文章内容
     */
    @Override
    public List getPageInfo(int pageId) {
        List pageContent = null;

        try {
            pageContent = jdbcTemplate.queryForList(SQLAccess.SelectPageInfo, new Object[]{pageId});
        } catch (EmptyResultDataAccessException exception) {
            LOGGER.error("根据pageId获取不到文章内容,pageId: " + pageId);
        }
        return pageContent;
    }

    /**
     * @param pageId
     * @return
     * @Description: TODO(获取除正文之外其他信息)
     * @Title: getPageOtherInfo
     */
    @Override
    public Map<String, Object> getPageOtherInfo(int userId, int pageId) {

        String sql = "";

        Map<String, Object> pageOtherInfoMap = new HashMap<>();

        /**
         * 根据pageId取出praNum,criNum,typeId,并根据typeId判断用户是否订阅了此分类
         */
        boolean isSubscri = false;

        sql = "SELECT praNum,criNum,typeId FROM page_info WHERE pageId=?";
        Map<String, Object> queryMap1 = jdbcTemplate.queryForMap(sql, new Object[]{pageId});

        sql = "SELECT thirdTypeName FROM type_info WHERE id=?";
        String thirdTypeName = jdbcTemplate.queryForObject(sql, new Object[]{(int) queryMap1.get("typeId")}, String.class);

        sql = "SELECT userFocusId FROM user_info WHERE userId=?";

        String userFocusId = "";
        try {
            userFocusId = jdbcTemplate.queryForObject(sql, new Object[]{userId}, String.class);
        } catch (EmptyResultDataAccessException exception) {
            LOGGER.info("未登陆用户请求除html页面外的其他信息...");
        }

        if (userFocusId.contains(thirdTypeName)) {
            isSubscri = true;
        }

        //--------------------到了添加数据的时候-----------------//
        pageOtherInfoMap.put("praNum", (int) queryMap1.get("praNum"));
        pageOtherInfoMap.put("criNum", (int) queryMap1.get("criNum"));
        pageOtherInfoMap.put("adPicUrl", Properties.Page_adPicUrl + Properties.ImageQuality);
        pageOtherInfoMap.put("adLink", "null");
        pageOtherInfoMap.put("isSubscri", isSubscri);

        /**
         * 从info_comment得到用户是否对该篇文章点击好文或者水文
         */
        int userChoice = -1;
        sql = "SELECT id,choice FROM info_comment WHERE userId=? AND pageId=?";
        try {
            // 防止info_comment有多个userId=-1而pageId相同的记录造成查询出错
            int userIdTemp = 0;
            if (userId != -1) {
                userIdTemp = userId;
            }

            Map<String, Object> queryMap2 = jdbcTemplate.queryForMap(sql, new Object[]{userIdTemp, pageId});
            userChoice = (int) queryMap2.get("choice");
        } catch (EmptyResultDataAccessException ex) {
            LOGGER.info("从info_comment中无法找到用户: " + userId + " 对pageId为：" + pageId + " 的文章的评价");
        }

        //--------------------到了添加数据的时候-----------------//
        pageOtherInfoMap.put("userChoice", userChoice);

        /**
         * 获取推荐文章列表,文章是随机的
         */
        List<Map<String, Object>> recomList = new ArrayList<>();

        try {
            recomList = jdbcTemplate.queryForList(SQLAccess.SelectRecommendPage, new Object[]{(int) queryMap1.get("typeId")});
        } catch (EmptyResultDataAccessException exception) {
            LOGGER.error("获取推荐文章出错，没有找到数据");
        }

        //--------------------到了添加数据的时候-----------------//
        pageOtherInfoMap.put("recomArtc", recomList);

        /**
         * 获取评论列表
         */
        List<Map<String, Object>> commentList = jdbcTemplate.queryForList(SQLAccess.SelectFirstPageComment, new Object[]{pageId, Properties.PageSize_PageComment});


        for (int i = 0; i < commentList.size(); i++) {
            commentList.get(i).put("hasPraised", checkPraised((int) commentList.get(i).get("commentId"), userId));
        }

        //--------------------到了添加数据的时候-----------------//
        pageOtherInfoMap.put("comFirPage", commentList);


        /**
         * 获取评论总条数
         */
        int commentNum = jdbcTemplate.queryForObject(SQLAccess.SelectPageCommentCount, new Object[]{pageId}, Integer.class);

        //--------------------到了添加数据的时候-----------------//
        pageOtherInfoMap.put("comNum", commentNum);

        return pageOtherInfoMap;
    }

    /**
     * @param userId
     * @param firstType
     * @param pageNumber
     * @param pageSize
     * @return
     * @name: getPageList
     * @description: 获取登陆用户主页面预览信息数据
     * @description: 图片传递url
     */
    @Override
    public List getPageList(int userId, int firstType, int pageNumber,
                            int pageSize) {

        // 未登陆用户主界面信息列表
        List mainPageList = new ArrayList<>();
        // 用户关注三级分类ID数组
        List<Integer> userFocusTypeList = null;

        try {
            userFocusTypeList = getUserFocusId(userId, firstType);
        } catch (Exception ex) {
            LOGGER.error("查询用户关注分类信息错误,Exception during getUserFocusId...", ex);
        }
        LOGGER.info("获取登陆用户主页面预览信息数据,用户关注列表：" + userFocusTypeList.toString());
        //如果用户关注类别为空
        if (userFocusTypeList.isEmpty()) {
            return null;
        } else {
            // 对list进行升序排序
            Collections.sort(userFocusTypeList);

            // 对于兴趣分类降序排序,将跬步有曰放在最后
            if (firstType == 5) {
                Collections.reverse(userFocusTypeList);
            }
        }

        int count = (userFocusTypeList.size() % pageSize == 0) ? userFocusTypeList.size() / pageSize : userFocusTypeList.size() / pageSize + 1;

        if (pageNumber > count) {
            LOGGER.info("用户请求分页数过大...");
            return null;
        }

        int begin = pageSize * (pageNumber - 1);
        int end = pageSize * (pageNumber);
        if (userFocusTypeList.size() <= pageSize * (pageNumber)) {
            end = userFocusTypeList.size();
        }

        for (int i = begin; i < end; i++) {

            // 这里使用了缓存方法
            List tempList = getThirdInfo(userFocusTypeList.get(i), firstType, Properties.PageSize_MainPagePerThrid);
            mainPageList.addAll(tempList);
        }

        if (mainPageList == null || mainPageList.isEmpty()) {
            LOGGER.error("mainPageList==null||mainPageList.isEmpty()");
        }
        return mainPageList;
    }


    /**
     * @param typeId
     * @param firstType
     * @param pageSize
     * @return
     * @description: 获取主界面上一个三级分类的信息
     */
    @Cacheable(value = {"thirdTypePageCacheVersion1_1"})
    public List getThirdInfo(int typeId, int firstType, int pageSize) {
        return jdbcTemplate.queryForList(SQLAccess.SelectAThirdTypeInfo, new Object[]{typeId, firstType, pageSize});
    }

    /**
     * @description: 获取一级分类主界面信息
     */
    @Override
    public MainPageModel getNewMainPage(int userId, int firstType, int pageNumber, int pageSize) {
        List pageList = getPageList(userId, firstType, pageNumber, pageSize);
        List titleList = null;
        boolean isLast = false;

        if (pageList == null || pageList.isEmpty()) {
            isLast = true;
        }

        if (pageNumber == 1) {
            titleList = getTitleList(firstType);
        }

        return new MainPageModel(pageList, isLast, titleList);
    }


    /**
     * @param secondType
     * @param thirdTypeName
     * @param pageNumber
     * @param pageSize
     * @return
     * @name: getThirdPageList
     * @description: 获取三级分类页面预览信息数据
     * @description: 图片传递url
     */
    @Override
    public List getThirdPageList(int secondType, String thirdTypeName,
                                 int pageNumber, int pageSize) {

        @SuppressWarnings("deprecation")
        int count = jdbcTemplate.queryForInt(SQLAccess.SelectAThirdTypeInfoCount, new Object[]{
                secondType, thirdTypeName});
        if ((pageNumber - 1) * pageSize >= count) {
            LOGGER.info("获取三级分类页面预览信息数据,用户请求分页数过大...");
            return null;
        }

        return jdbcTemplate.queryForList(SQLAccess.SelectAThirdTypePageInfo, new Object[]{
                secondType, thirdTypeName, (pageNumber - 1) * pageSize,
                pageSize});
    }

    /**
     * @param userId
     * @param firstType
     * @return
     * @name: getUserFocusId
     * @description: 得到用户关注的一级分类下的三级分类字符串组合
     */
    @Override
    public List<Integer> getUserFocusId(int userId, int firstType) {

        // 用户关注三级分类ID数组
        List<Integer> userFocusTypeList = new ArrayList<>();


        // 取得数据库中用户关注Json字符串
        String focusId = jdbcTemplate.queryForObject(SQLAccess.SelectUserFocusInfo, new Object[]{userId},
                String.class);

        // String转JSONObject对象
        JSONObject jsonObject = JSONObject.fromObject(focusId);
        JSONArray jsonArray = jsonObject.getJSONArray("focusList");

        // 存储用户关注的二级分类id和三级分类name
        List<Focus> focusList = new ArrayList<Focus>();

        // 从JSONObject中取出用户关注信息加入到focusList中
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject json = jsonArray.getJSONObject(i);
            if (json.getInt("itemNumber") == firstType) {
                JSONArray jsArray = json.getJSONArray("focus");
                for (int j = 0; j < jsArray.size(); j++) {
                    JSONObject js = jsArray.getJSONObject(j);
                    Focus focus = new Focus();
                    focus.setSecondType(js.getInt("secondType"));
                    focus.setThirdTypeName(js.getString("thirdName"));
                    focusList.add(focus);
                }
            }
        }

        String userFocusIdString = "";

        // 循环从数据库查询出用户关注的三级分类的id

        int k = 0;

        try {
            for (k = 0; k < focusList.size(); k++) {

                int id = jdbcTemplate.queryForObject(SQLAccess.SelectThirdTypeId, new Object[]{
                                focusList.get(k).getSecondType(),
                                focusList.get(k).getThirdTypeName()},
                        Integer.class);
                userFocusTypeList.add(id);
                // userFocusIdString = userFocusIdString.concat(id + ",");
            }
        } catch (EmptyResultDataAccessException e) {
            LOGGER.error("根据secondType和thirdName找不到三级分类id,secondType: " + focusList.get(k).getSecondType() + ",thirdName: " + focusList.get(k).getThirdTypeName(), e);
        }

        return userFocusTypeList;
    }

    /**
     * @param secondFocusString
     * @param pageNumber
     * @param pageSize
     * @return
     * @name: getSecondPageList
     * @description: 获取二级分类页面预览信息数据
     * @description: 图片传递url
     */
    @Override
    public List getSecondPageList(String secondFocusString, int pageNumber,
                                  int pageSize) {
        // String转JSONObject对象
        JSONObject jsonObject = JSONObject.fromObject(secondFocusString);
        int secondType = jsonObject.getInt("secondType");
        String thirdNameListString = jsonObject.getString("thirdNameList");

        // 存储用户关注的二级分类id和三级分类name
        String[] thirdNameList = thirdNameListString.split(",");

        String userFocusIdString = "";

        // 循环从数据库查询出用户关注的三级分类的id
        int k = 0;
        try {
            for (k = 0; k < thirdNameList.length; k++) {
                int id = jdbcTemplate.queryForObject(SQLAccess.SelectThirdTypeId, new Object[]{
                        secondType, thirdNameList[k]}, Integer.class);
                userFocusIdString = userFocusIdString.concat(id + ",");
            }
        } catch (EmptyResultDataAccessException e) {
            LOGGER.error("根据secondType和thirdName找不到三级分类id,EmptyResultDataAccessException...", e);
            LOGGER.error("secondType: " + secondType + " ,thirdTypeName: " + thirdNameList[k]);
        }

        userFocusIdString = userFocusIdString.substring(0,
                userFocusIdString.length() - 1);

        String sql = "SELECT pageId,imageSrc,firstTitle,readNumber,thirdTypeName FROM pagepre_second WHERE typeId IN ("
                + userFocusIdString + ") ORDER BY pageDate DESC LIMIT ?,?";
        return jdbcTemplate.queryForList(sql, new Object[]{
                (pageNumber - 1) * pageSize, pageSize});
    }


    /**
     * @param pageId
     * @name: doRead
     * @description: 记录用户阅读
     */
    @Override
    public boolean doRead(int pageId) {
        try {
            jdbcTemplate.update(SQLAccess.UpdatePageReadNumberPlus, new Object[]{pageId});
        } catch (Exception exception) {
            LOGGER.error("记录用户阅读失败", exception);
            return false;
        }
        return true;
    }

    /**
     * 记录文章被转发(分享)
     *
     * @param pageId
     * @return
     */
    @Override
    public boolean doTransmitCount(int pageId) {
        /**
         * 更新文章的转发量"transmitNumber"加一
         */
        try {
            jdbcTemplate.update(SQLAccess.UpdatePageTransmitNumberPlus, pageId);
        } catch (Exception exception) {
            LOGGER.error("更新文章转发量错误...", exception);
            return false;
        }
        return true;
    }

    /**
     * @param userId
     * @param pageId
     * @param commentType
     * @return
     * @Description: TODO(评论文章水文/好文)
     */
    @Override
    public boolean pageContentComment(int userId, int pageId, int commentType) {

        try {
            jdbcTemplate.update(SQLAccess.AddAPageInfoComment, new Object[]{userId, pageId, commentType});

            /**
             * 根据用户评论水文好文，改变page_info中praNum和criNum的数值
             */
            if (commentType == 0) {
                jdbcTemplate.update(SQLAccess.UpdatePageCriNumberPlus, new Object[]{pageId});
            } else {
                jdbcTemplate.update(SQLAccess.UpdatePagePraiseNumberPlus, new Object[]{pageId});
            }
        } catch (DataAccessException exception) {
            LOGGER.error("评论文章水文/好文出错,插入数据库是出错");
            return false;
        }

        return true;
    }

    /**
     * @param userId
     * @param wantedInfo
     * @param infoSource
     * @return
     * @Description: 在文章中对三级分类信息进行评论和提供信息源
     */
    @Override
    public boolean postComInPage(int userId, String wantedInfo, String infoSource) {
        int reflectRows = jdbcTemplate.update(SQLAccess.AddAPageInfoFeedback, new Object[]{userId, wantedInfo, infoSource, new Date()});

        if (reflectRows == 1) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * @param commentId
     * @return
     * @Description: TODO(获取评论的回复列表)
     */
    @Override
    public List getCommentReply(int commentId) {
        return jdbcTemplate.queryForList(SQLAccess.SelectCommentReply, new Object[]{commentId});
    }

    /**
     * @param mainFocusList
     * @param pageNumber
     * @param pageSize
     * @return
     * @name: getPageListNoLogin
     * @description: 得到未登陆用户的主界面一级分类下的预览信息数据
     * @description: 图片传递url
     */
    @Override
    public List getPageListNoLogin(String mainFocusList, int pageNumber,
                                   int pageSize) {

        // 未登陆用户主界面信息列表
        List mainPageList = new ArrayList<>();
        // 用户关注三级分类ID数组
        List<Integer> focusTypeIdList = new ArrayList<>();

        // String转JSONObject对象
        JSONObject jsonObject = JSONObject.fromObject(mainFocusList);
        JSONArray jsonArray = jsonObject.getJSONArray("mainFocusList");

        // String userFocusIdString = "";

        // 循环从数据库查询出用户关注的三级分类的id
        int k = 0;
        try {
            for (k = 0; k < jsonArray.size(); k++) {
                // jdbcTemplate在执行queryForObject方法时，如果得到的结果不是1则会抛出异常

                int id = jdbcTemplate.queryForObject(SQLAccess.SelectThirdTypeId, new Object[]{
                                jsonArray.getJSONObject(k).getInt("secondType"),
                                jsonArray.getJSONObject(k).getString("thirdName")},
                        Integer.class);
                focusTypeIdList.add(id);
                // userFocusIdString = userFocusIdString.concat(id + ",");
            }
        } catch (EmptyResultDataAccessException exception) {
            LOGGER.error("得到未登陆用户的主界面一级分类下的预览信息数据错误,mainFocusList: \n" + mainFocusList + "\n");
        }

        if (focusTypeIdList.isEmpty()) {
            LOGGER.info("关注分类数为0...");
            return null;
        } else {
            // 对list进行升序排序
            Collections.sort(focusTypeIdList);
        }

        int count = (focusTypeIdList.size() % pageSize == 0) ? focusTypeIdList.size() / pageSize : focusTypeIdList.size() / pageSize + 1;

        if (pageNumber > count) {
            LOGGER.info("用户请求分页数过大...");
            return null;
        }

        int begin = pageSize * (pageNumber - 1);
        int end = pageSize * (pageNumber);
        if (focusTypeIdList.size() <= pageSize * (pageNumber)) {
            end = focusTypeIdList.size();
        }


        for (int i = begin; i < end; i++) {

            // 使用了缓存方法
            List tempList = getThirdInfoNoLogin(focusTypeIdList.get(i), Properties.PageSize_MainPagePerThrid);
            mainPageList.addAll(tempList);
        }

        if (mainPageList == null || mainPageList.isEmpty()) {
            LOGGER.error("mainPageList==null||mainPageList.isEmpty()");
        }
        return mainPageList;
    }


    /**
     * 获取未登陆用户主界面单个三级分类信息
     *
     * @param typeId
     * @param pageSize
     * @return
     */
    @Cacheable(value = {"noLoginThirdPageVersion1_1"})
    public List getThirdInfoNoLogin(int typeId, int pageSize) {
        return jdbcTemplate.queryForList(SQLAccess.SelectAThirdTypeInfoNoLogin, new Object[]{typeId, pageSize});
    }


    /**
     * @param firstType
     * @return
     * @name: getTitleList
     * @description: 获取主界面大图信息数组
     * @description: 图片传递url
     */
    @Override
    @Cacheable(value = {"pageTitleVersion1_1"})
    public List getTitleList(int firstType) {
        //大图信息数组
        List titleList = null;
        try {
            titleList = jdbcTemplate.queryForList(SQLAccess.SelectFirstTypeSlideInfo, new Object[]{firstType, Properties.TitlePic_Num});
        } catch (EmptyResultDataAccessException exception) {
            LOGGER.error("获取主界面大图信息数组,未查询到大图信息,EmptyResultDataAccessException...", exception);
        }
        return titleList;
    }
}
